import pymysql


class DBTools:
    @classmethod
    def __connect_db(cls):
        # conn = pymysql.connect(host='192.168.70.10', port=3306,
        #                        user='root', password='123456',
        #                        database='test_db', charset='utf8')
        #
        conn = pymysql.connect(host='211.103.136.244', port=7061,
                       user='student', password='iHRM_student_2022',
                       database='ihrm', charset='utf8')
        return conn

    @classmethod
    def get_one(cls, sql):
        """查询语句,获取单条数据"""
        conn = None
        cursor = None
        res = None

        try:
            # 获取连接
            conn = cls.__connect_db()
            # 创建游标
            cursor = conn.cursor()
            # 执行 sql 语句
            cursor.execute(sql)
            # 获取结果
            res = cursor.fetchone()
        except Exception as e:
            print('发生了错误', e)
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            # 需要将结果返回出去
            return res

    @classmethod
    def uid_db(cls, sql):
        """增删改操作"""
        cursor = None
        conn = None

        try:
            # 获取连接
            conn = cls.__connect_db()
            # 获取游标
            cursor = conn.cursor()
            # 执行 sql
            cursor.execute(sql)
            # 提交事务
            conn.commit()
        except Exception as e:
            # 回滚事务
            conn.rollback()
            print('发生了错误', e)
        finally:
            cursor.close()
            conn.close()


if __name__ == '__main__':
    sql = 'select * from t_book;'
    result = DBTools.get_one(sql)
    print(result)
    DBTools.uid_db("update t_book  set `read` = `read` + 1 where title = '西游记新编';")
